package servlet;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;
import java.sql.SQLException;

public class Function {
//	public static Connection connection = null;
//	public static Statement statement = null;
//	public static ResultSet resultSet = null;
	public static DecimalFormat df = new DecimalFormat(".00");
	public static  Connection connect(Connection c) {
		try {
			Class.forName("org.sqlite.JDBC");
			c = DriverManager.getConnection("jdbc:sqlite:C:/Users/Administrator/MiaoAccount.db");
		} catch (Exception e) {
			System.err.println(e.getClass().getName() + ": " + e.getMessage());
			System.exit(0);
		}
		return c;
	}

	// 添加用户 解决
	public static void insertopenid(String openid) {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select *from users where openid='" + openid + "';";
			resultSet = statement.executeQuery(sql);
			if (!resultSet.next()) { // 在数据库中不存在该用户
				sql = "insert into users(openid) values(" + "\"" + openid + "\"" + ");";
				statement.executeUpdate(sql);
			}
		} catch (Exception e) {
				e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
			}
		}
	}
	// 获取邮箱信息，如果没有就是empty
	public static String findemailmessage(String openid) {
		String email = "您暂未绑定邮箱";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select email from users where openid=" + "\"" + openid + "\";");
			if (resultSet.next()) {
				if(resultSet.getString(1)!=null){
					email = resultSet.getString(1);
					System.out.println(email);
				}
			}
		} catch (Exception e) {
			//e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
			}
		}
		
		return email;
	}

	// 插入邮箱信息 ok
	public static void insertemail(String openid, String email) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "update users set email=\"" + email + "\" where openid=" + "\"" + openid + "\";";
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}

		}

	}

	// 插入提醒的内容 ok
	public static void insertemailtip(String openid, String emailtip) throws SQLException {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "update users set emailtip=\"" + emailtip + "\" where openid=" + "\"" + openid + "\";";
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}

		}
	}

	// 获取提醒内容，如果是empty就是空的意思 ok
	public static String findemailtip(String openid) {
		String emailtip = "选此不设记账提醒";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		int flag=0;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select emailtip from users where openid=" + "\"" + openid + "\";";
			resultSet = connection.createStatement().executeQuery(sql);
			if (resultSet.next()) {
				if(resultSet.getString(1)!=null){
				emailtip = resultSet.getString(1);
				flag=1;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
			}
		}
		if(flag==0){   //说明在数据库中没有找到记录
			try {
				insertemailtip(openid,emailtip);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return emailtip;
	}

	// 记一笔 ok
	public static void insertaccount(String openid, String tname, String tip, float money) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "insert into account(openid,tname,tip,money) " + "values(" + "\"" + openid + "\"," + "\""
					+ tname + "\"," + "\"" + tip + "\"," + Float.parseFloat(df.format(money)) + ");";
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.commit();
				connection.close();
			} catch (SQLException e) {

			}

		}

	}

	// 查看明细 ok
	public static ArrayList<Account> findaccount(String openid, int year, int month) {
		ArrayList<Account> account = new ArrayList<>();
		Connection connection = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			String time = year + "-" + month, next, sql;
			if (month < 10) {
				time = year + "-0" + month;
			} else {
				time = year + "-" + month;
			}
			if (month == 12) {
				year += 1;
				month = 1;
			} else {
				month++;
			}
			if (month < 10) {
				next = year + "-0" + month;
			} else {
				next = year + "-" + month;
			}
			sql = "select * from account where openid=" + "\"" + openid + "\"" + " and currenttime > \"" + time
					+ "\" and currenttime < \"" + next + "\";";
			resultSet = connection.createStatement().executeQuery(sql);
			while (resultSet.next()) {
				// System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)+","+rs.getString(4)+","+rs.getFloat(5));
				account.add(new Account(resultSet.getString(1), resultSet.getString(2), resultSet.getString(3),
						resultSet.getString(4), resultSet.getFloat(5)));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				connection.commit();
				connection.close();
			} catch (SQLException e) {

			}

		}
		return account;
	}

	// 查看月总收入支出 ok
	public static float[] findin_out(String openid, int year, int month) {
		float[] total = new float[3];
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String time, next;
			if (month < 10) {
				time = year + "-0" + month;
			} else {
				time = year + "-" + month;
			}
			if (month == 12) {
				year += 1;
				month = 1;
			} else {
				month++;
			}
			if (month < 10) {
				next = year + "-0" + month;
			} else {
				next = year + "-" + month;
			}
			String sql = "select money from account where openid=" + "\"" + openid + "\"" + " and currenttime > \""
					+ time + "\" and currenttime < \"" + next + "\";";
			total[0] = 0;
			total[1] = 0;
			total[2] = 0;
			resultSet = statement.executeQuery(sql);
			float getmoney;
			while (resultSet.next()) {
				getmoney = resultSet.getFloat(1);
				if (getmoney < 0) {
					total[0] += getmoney;
				} else {
					total[1] += getmoney;
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				
			}
		}
		
		total[0] = -1 * total[0];
		total[2] = total[1] - total[0];
		return total;
	}

	// 设置月预算 ok
	public static void insertbudget(String openid, float money) {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			Calendar now = Calendar.getInstance();
			int year = now.get(Calendar.YEAR);
			int month = now.get(Calendar.MONTH) + 1;
			String sql = "update budget set money=" + Float.parseFloat(df.format(money)) + " where openid=" + "\""
					+ openid + "\"" + " and year=" + year + " and month=" + month + ";";
			statement.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
	}

	// 查看月预算 ok
	public static float findbudget(String openid, int year, int month) {
		float money = 0.0f;
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select money from budget" + " where openid=" + "\"" + openid + "\"" + " and year=" + year
					+ " and month=" + month + ";";
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) { // 如果有月预算，则从数据库获取
				money = resultSet.getFloat("money");
			} else { //    如果没有就默认插入0的月预算
				sql = "insert into budget values('"+openid+"',"+year+","+month+","+money+");";
				statement.executeUpdate(sql);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				resultSet.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return money;
	}

	// 记账天数 ok
	public static int accountdays(String openid) {
		Set<String> set = new HashSet<String>();
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String sql = "select currenttime from account where openid=" + "\"" + openid + "\";";
			// System.out.println(sql);
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				set.add(resultSet.getString(1).substring(0, 10));// 只截取年月日
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			try {
				statement.close();
				connection.commit();
				connection.close();
				resultSet.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return set.size();
	}

	// 报表收入和支出 ok
	public static ArrayList<pdata> programdata(String openid, int year1, int month1, int year2, int month2,
			String in_or_out){
		Map<String, Float> map = new HashMap<>();
		ArrayList<pdata> list1 = new ArrayList<>();
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		ResultSet resultSet2 =null;
		try {
			connection = Function.connect(connection);
			connection.setAutoCommit(false);
			statement = connection.createStatement();
			String time1 = year1 + "-" + month1, time2 = year2 + "-" + month2;
			// 对时间进行处理
			if (month1 < 10) {
				time1 = year1 + "-0" + month1;
			} else {
				time1 = year1 + "-" + month1;
			}
			if (month2 < 10) {
				time2 = year2 + "-0" + month2 + "-9";
			} else {
				time2 = year2 + "-" + month2 + "-9";
			}
			String sql, sql2;
			// 针对收入或者支出的数据请求
			if (in_or_out.equals("income")) {
				sql = "select tname from account where openid=" + "\'" + openid + "\' and currenttime > \"" + time1
						+ "\" and currenttime < \"" + time2 + "\"  and money>0;";
				sql2 = "select tname, money from account where openid=" + "\'" + openid + "\' and currenttime > \""
						+ time1 + "\" and currenttime < \"" + time2 + "\" and money>0;";
			} else {
				sql = "select tname from account where openid=" + "\'" + openid + "\' and currenttime > \"" + time1
						+ "\" and currenttime < \"" + time2 + "\"  and money<0;";
				sql2 = "select tname, money from account where openid=" + "\'" + openid + "\' and currenttime > \""
						+ time1 + "\" and currenttime < \"" + time2 + "\" and money<0;";
			}
			resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				if (!map.containsKey(resultSet.getString(1))) {
					map.put(resultSet.getString(1), 0f);
				}
			}
			resultSet2= statement.executeQuery(sql2);
			while (resultSet2.next()) {
				map.put(resultSet2.getString(1), map.get(resultSet2.getString(1)) + resultSet2.getFloat(2));
			}
			for (Entry<String, Float> entry : map.entrySet()) {
				list1.add(new pdata(entry.getKey(), entry.getValue()));
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			try {
				resultSet.close();
				resultSet2.close();
				statement.close();
				connection.commit();
				connection.close();
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}
		return list1;
	}

}
